﻿using System;
using System.Data;
using System.Data.SqlClient;

//string connectionString = "Data Source=你的服务器名;Initial Catalog=library_2;Integrated Security=True";
//// 如果使用 SQL Server 账号密码登录：
//string connectionString = "Data Source=你的服务器名;Initial Catalog=library_2;User ID=sa;Password=你的密码;";


namespace LibrarySystem
{
    public class LibraryDatabaseHelper
    {
        private readonly string connectionString;

        public LibraryDatabaseHelper(string connStr)
        {
            this.connectionString = connStr;
        }

        // ================== BOOKS 表 ==================
        public void AddBook(string title, string author, string publisher, string cover_image_url, string location,
                            string description, int stock, int borrow_count, string category)
        {
            string query = @"INSERT INTO library_db.books 
                (title, author, publisher, cover_image_url, location, description, stock, borrow_count, category)
                VALUES (@title, @auth, @pub, @img, @loc, @desc, @stock, @count, @cat)";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@title", title);
                cmd.Parameters.AddWithValue("@auth", author);
                cmd.Parameters.AddWithValue("@pub", publisher);
                cmd.Parameters.AddWithValue("@img", cover_image_url);
                cmd.Parameters.AddWithValue("@loc", location);
                cmd.Parameters.AddWithValue("@desc", description);
                cmd.Parameters.AddWithValue("@stock", stock);
                cmd.Parameters.AddWithValue("@count", borrow_count);
                cmd.Parameters.AddWithValue("@cat", category);
            });
        }

        public void UpdateBook(int bookId, string title, string author, string publisher, string cover_image_url,
                               string location, string description, int stock, int borrow_count, string category)
        {
            string query = @"UPDATE library_db.books SET 
                title=@title, author=@auth, publisher=@pub, cover_image_url=@img,
                location=@loc, description=@desc, stock=@stock, borrow_count=@count, category=@cat
                WHERE book_id = @id";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@title", title);
                cmd.Parameters.AddWithValue("@auth", author);
                cmd.Parameters.AddWithValue("@pub", publisher);
                cmd.Parameters.AddWithValue("@img", cover_image_url);
                cmd.Parameters.AddWithValue("@loc", location);
                cmd.Parameters.AddWithValue("@desc", description);
                cmd.Parameters.AddWithValue("@stock", stock);
                cmd.Parameters.AddWithValue("@count", borrow_count);
                cmd.Parameters.AddWithValue("@cat", category);
                cmd.Parameters.AddWithValue("@id", bookId);
            });
        }

        public void DeleteBook(int book_id)
        {
            string query = "DELETE FROM library_db.books WHERE book_id = @id";
            ExecuteNonQuery(query, cmd => cmd.Parameters.AddWithValue("@id", book_id));
        }

        public DataTable GetBooks()
        {
            return ExecuteQuery("SELECT * FROM library_db.books");
        }

        // ================== USERS 表 ==================
        public void AddUser(string username, string password, string phone, string studentId, bool permission, string request)
        {
            string query = @"INSERT INTO library_db.users 
                (username, password, phone, student_id, permission,request)
                VALUES (@u, @p, @ph, @sid, @perm,@req)";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@p", password);
                cmd.Parameters.AddWithValue("@ph", phone);
                cmd.Parameters.AddWithValue("@sid", studentId);
                cmd.Parameters.AddWithValue("@perm", permission);
                cmd.Parameters.AddWithValue("@req", request);
            });
        }

        public void UpdateUser(int userId, string username, string phone)
        {
            string query = @"UPDATE library_db.users SET username=@u, phone=@ph WHERE user_id=@id";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@ph", phone);
                cmd.Parameters.AddWithValue("@id", userId);
            });
        }

        public void DeleteUser(int userId)
        {
            string query = "DELETE FROM library_db.users WHERE user_id = @id";
            ExecuteNonQuery(query, cmd => cmd.Parameters.AddWithValue("@id", userId));
        }

        public DataTable GetUsers()
        {
            return ExecuteQuery("SELECT * FROM library_db.users");
        }

        // ================== ADMINS 表 ==================
        public void AddAdmin(string username, string password)
        {
            string query = @"INSERT INTO library_db.admins (username, password)
                             VALUES (@u, @p)";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@p", password);
            });
        }

        public void UpdateAdmin(int adminId, string username)
        {
            string query = @"UPDATE library_db.admins SET username=@u WHERE admin_id=@id";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@id", adminId);
            });
        }

        public void DeleteAdmin(int adminId)
        {
            string query = "DELETE FROM library_db.admins WHERE admin_id = @id";
            ExecuteNonQuery(query, cmd => cmd.Parameters.AddWithValue("@id", adminId));
        }

        public DataTable GetAdmins()
        {
            return ExecuteQuery("SELECT * FROM library_db.admins");
        }

        // ================== BorrowRecords 表 ==================
        public void AddBorrowRecord(int userId, int bookId, DateTime borrowTime, DateTime? returnTime = null)
        {
            string query = @"INSERT INTO dbo.BorrowRecords (user_id, book_id, borrow_time, return_time)
                             VALUES (@uid, @bid, @bt, @rt)";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@uid", userId);
                cmd.Parameters.AddWithValue("@bid", bookId);
                cmd.Parameters.AddWithValue("@bt", borrowTime);
                if (returnTime.HasValue)
                    cmd.Parameters.AddWithValue("@rt", returnTime.Value);
                else
                    cmd.Parameters.AddWithValue("@rt", DBNull.Value);
            });
        }

        public void UpdateReturnTime(int recordId, DateTime returnTime)
        {
            string query = "UPDATE dbo.BorrowRecords SET return_time=@rt WHERE record_id=@id";
            ExecuteNonQuery(query, cmd => {
                cmd.Parameters.AddWithValue("@rt", returnTime);
                cmd.Parameters.AddWithValue("@id", recordId);
            });
        }

        public void DeleteBorrowRecord(int recordId)
        {
            string query = "DELETE FROM dbo.BorrowRecords WHERE record_id=@id";
            ExecuteNonQuery(query, cmd => cmd.Parameters.AddWithValue("@id", recordId));
        }

        public DataTable GetBorrowRecords()
        {
            return ExecuteQuery("SELECT * FROM dbo.BorrowRecords");
        }

        // ================== 工具方法 ==================
        private void ExecuteNonQuery(string query, Action<SqlCommand> parameterize)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                parameterize(cmd);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        private DataTable ExecuteQuery(string query)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }
}
